package com.governtalk.backend.repository;

import com.governtalk.backend.entity.Post;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface PostRepository extends JpaRepository<Post, Long> {
    
    Page<Post> findByStatus(Post.PostStatus status, Pageable pageable);
    
    Page<Post> findByCategoryIdAndStatus(Long categoryId, Post.PostStatus status, Pageable pageable);
    
    @Query("SELECT p FROM Post p WHERE p.status = 'APPROVED' AND (p.title LIKE %:keyword% OR p.content LIKE %:keyword%)")
    Page<Post> findByKeywordAndStatus(@Param("keyword") String keyword, Pageable pageable);
    
    @Query("SELECT p FROM Post p WHERE p.status = 'APPROVED' ORDER BY (p.likeCount * 2 + p.commentCount * 3 + p.viewCount * 0.1) DESC")
    List<Post> findHotPosts(Pageable pageable);
    
    @Query("SELECT p FROM Post p WHERE p.status = 'APPROVED' ORDER BY p.createdAt DESC")
    List<Post> findLatestPosts(Pageable pageable);
    
    @Query("SELECT p FROM Post p WHERE p.status = 'PENDING'")
    Page<Post> findPendingPosts(Pageable pageable);
    
    Page<Post> findByUserId(Long userId, Pageable pageable);
    
    @Query("SELECT p FROM Post p LEFT JOIN FETCH p.user WHERE p.id = :postId")
    Optional<Post> findByIdWithUser(@Param("postId") Long postId);
}
